Moving from one database vendor to another can sometimes be difficult due to syntax differences between data types, functions, and language elements. Db2 already has a high degree of compatibility with Oracle PLSQL along with some of the Oracle data types.
Db2 11 introduces some additional data type and function compatibility that will reduce some of the migration effort required when porting from other systems. There are some specific features within Db2 that are targeted at Netezza SQL and that is discussed in a separate section.
In [ ]:
%run db2.ipynb
We populate the database with the EMPLOYEE and DEPARTMENT tables so that we can run the various examples.
In [ ]:
%sql -sampledata
Db2 allows the use of the Oracle outer-join operator when Oracle compatibility is turned on within a database. In Db2 11, the outer join operator is available by default and does not require the DBA to turn on Oracle compatibility.
Db2 supports standard join syntax for LEFT and RIGHT OUTER JOINS. However, there is proprietary syntax used by Oracle employing a keyword: "(+)" to mark the "null-producing" column reference that precedes it in an implicit join notation. That is (+) appears in the WHERE clause and refers to a column of the inner table in a left outer join.
For instance:
SELECT * FROM T1, T2 WHERE T1.C1 = T2.C2 (+)Is the same as:
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C2
In this example, we get list of departments and their employees, as well as the names of departments who have no employees.
This example uses the standard Db2 syntax.
In [ ]:
%%sql
SELECT DEPTNAME, LASTNAME FROM
DEPARTMENT D LEFT OUTER JOIN EMPLOYEE E
ON D.DEPTNO = E.WORKDEPT
This example works in the same manner as the last one, but uses the "+" sign syntax. The format is a lot simpler to remember than OUTER JOIN syntax, but it is not part of the SQL standard.
In [ ]:
%%sql
SELECT DEPTNAME, LASTNAME FROM
DEPARTMENT D, EMPLOYEE E
WHERE D.DEPTNO = E.WORKDEPT (+)
In [ ]:
%%sql -q
DROP TABLE LONGER_CHAR;
CREATE TABLE LONGER_CHAR
(
NAME CHAR(255)
);
Db2 11 introduces two new binary data types: BINARY and VARBINARY. These two data types can contain any combination of characters or binary values and are not affected by the codepage of the server that the values are stored on.
A BINARY data type is fixed and can have a maximum length of 255 bytes, while a VARBINARY column can contain up to 32672 bytes. Each of these data types is compatible with columns created with the FOR BIT DATA keyword.
The BINARY data type will reduce the amount of conversion required from other data bases. Although binary data was supported with the FOR BIT DATA clause on a character column, it required manual DDL changes when migrating a table definition.
This example shows the creation of the three types of binary data types.
In [ ]:
%%sql -q
DROP TABLE HEXEY;
CREATE TABLE HEXEY
(
AUDIO_SHORT BINARY(255),
AUDIO_LONG VARBINARY(1024),
AUDIO_CHAR VARCHAR(255) FOR BIT DATA
);
Inserting data into a binary column can be done through the use of BINARY functions, or the use of X'xxxx' modifiers when using the VALUE clause. For fixed strings you use the X'00' format to specify a binary value and BX'00' for variable length binary strings. For instance, the following SQL will insert data into the previous table that was created.
In [ ]:
%%sql
INSERT INTO HEXEY VALUES
(BINARY('Hello there'),
BX'2433A5D5C1',
VARCHAR_BIT_FORMAT(HEX('Hello there')));
SELECT * FROM HEXEY;
Handling binary data with a FOR BIT DATA column was sometimes tedious, so the BINARY columns will make coding a little simpler. You can compare and assign values between any of these types of columns. The next SQL statement will update the AUDIO_CHAR column with the contents of the AUDIO_SHORT column. Then the SQL will test to make sure they are the same value.
In [ ]:
%%sql
UPDATE HEXEY
SET AUDIO_CHAR = AUDIO_SHORT
We should have one record that is equal.
In [ ]:
%%sql
SELECT COUNT(*) FROM HEXEY WHERE
AUDIO_SHORT = AUDIO_CHAR
The boolean data type (true/false) has been available in SQLPL and PL/SQL scripts for some time. However, the boolean data type could not be used in a table definition. Db2 11 FP1 now allows you to use this data type in a table definition and use TRUE/FALSE clauses to compare values.
This simple table will be used to demonstrate how BOOLEAN types can be used.
In [ ]:
%%sql -q
DROP TABLE TRUEFALSE;
CREATE TABLE TRUEFALSE (
EXAMPLE INT,
STATE BOOLEAN
);
The keywords for a true value are TRUE, 'true', 't', 'yes', 'y', 'on', and '1'. For false the values are FALSE, 'false', 'f', 'no', 'n', and '0'.
In [ ]:
%%sql
INSERT INTO TRUEFALSE VALUES
(1, TRUE),
(2, FALSE),
(3, 0),
(4, 't'),
(5, 'no')
Now we can check to see what has been inserted into the table.
In [ ]:
%sql SELECT * FROM TRUEFALSE
Retrieving the data in a SELECT statement will return an integer value for display purposes. 1 is true and 0 is false (binary 1 and 0).
Comparison operators with BOOLEAN data types will use TRUE, FALSE, 1 or 0 or any of the supported binary values. You have the choice of using the equal (=) operator or the IS or IS NOT syntax as shown in the following SQL.
In [ ]:
%%sql
SELECT * FROM TRUEFALSE
WHERE STATE = TRUE OR STATE = 1 OR STATE = 'on' OR STATE IS TRUE
Db2 has the standard data types that most developers are familiar with, like CHAR, INTEGER, and DECIMAL. There are other SQL implementations that use different names for these data types, so Db2 11 now allows these data types as syonomys for the base types.
These data types are:
| Type | Db2 Equivalent |
|---|---|
| INT2 | SMALLINT |
| INT4 | INTEGER |
| INT8 | BIGINT |
| FLOAT4 | REAL |
| FLOAT8 | FLOAT |
The following SQL will create a table with all of these data types.
In [ ]:
%%sql -q
DROP TABLE SYNONYM_EMPLOYEE;
CREATE TABLE SYNONYM_EMPLOYEE
(
NAME VARCHAR(20),
SALARY INT4,
BONUS INT2,
COMMISSION INT8,
COMMISSION_RATE FLOAT4,
BONUS_RATE FLOAT8
);
When you create a table with these other data types, Db2 does not use these "types" in the catalog. What Db2 will do is use the Db2 type instead of these synonym types. What this means is that if you describe the contents of a table, you will see the Db2 types displayed, not these synonym types.
In [ ]:
%%sql
SELECT DISTINCT(NAME), COLTYPE, LENGTH FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME='SYNONYM_EMPLOYEE' AND TBCREATOR=CURRENT USER
Db2 has a wealth of built-in functions that are equivalent to competitive functions, but with a different name. In Db2 11, these alternate function names are mapped to the Db2 function so that there is no re-write of the function name required. This first SQL statement generates some data required for the statistical functions.
This command generates X,Y coordinate pairs in the xycoord table that are based on the function y = 2x + 5. Note that the table creation uses Common Table Expressions and recursion to generate the data!
In [ ]:
%%sql -q
DROP TABLE XYCOORDS;
CREATE TABLE XYCOORDS
(
X INT,
Y INT
);
INSERT INTO XYCOORDS
WITH TEMP1(X) AS
(
VALUES (0)
UNION ALL
SELECT X+1 FROM TEMP1 WHERE X < 10
)
SELECT X, 2*X + 5
FROM TEMP1;
In [ ]:
%%sql
SELECT 'COVAR_POP', COVAR_POP(X,Y) FROM XYCOORDS
UNION ALL
SELECT 'COVARIANCE', COVARIANCE(X,Y) FROM XYCOORDS
In [ ]:
%%sql
SELECT 'STDDEV_POP', STDDEV_POP(X) FROM XYCOORDS
UNION ALL
SELECT 'STDDEV', STDDEV(X) FROM XYCOORDS
In [ ]:
%%sql
SELECT 'VAR_SAMP', VAR_SAMP(X) FROM XYCOORDS
UNION ALL
SELECT 'VARIANCE_SAMP', VARIANCE_SAMP(X) FROM XYCOORDS
In [ ]:
%%sql
WITH EMP(LASTNAME, WORKDEPT) AS
(
VALUES ('George','A01'),
('Fred',NULL),
('Katrina','B01'),
('Bob',NULL)
)
SELECT * FROM EMP WHERE
WORKDEPT ISNULL
In [ ]:
%%sql
VALUES ('LOG',LOG(10))
UNION ALL
VALUES ('LN', LN(10))
In [ ]:
%%sql
VALUES ('RANDOM', RANDOM())
UNION ALL
VALUES ('RAND', RAND())
In [ ]:
%%sql
VALUES ('POSSTR',POSSTR('Hello There','There'))
UNION ALL
VALUES ('STRPOS',STRPOS('Hello There','There'))
In [ ]:
%%sql
VALUES ('LEFT',LEFT('Hello There',5))
UNION ALL
VALUES ('STRLEFT',STRLEFT('Hello There',5))
In [ ]:
%%sql
VALUES ('RIGHT',RIGHT('Hello There',5))
UNION ALL
VALUES ('STRRIGHT',STRRIGHT('Hello There',5))
Db2 provides features that enable applications that were written for a Netezza Performance Server (NPS) database to use a Db2 database without having to be rewritten.
The SQL_COMPAT global variable is used to activate the following optional NPS compatibility features:
In [ ]:
%%sql
WITH SPECIAL(OP, DESCRIPTION, EXAMPLE, RESULT) AS
(
VALUES
(' | ','OR ', '2 | 3 ', 2 | 3),
(' & ','AND ', '2 & 3 ', 2 & 3),
(' ^ ','XOR ', '2 ^ 3 ', 2 ^ 3),
(' ~ ','COMPLEMENT', '~2 ', ~2),
(' # ','NONE ', ' ',0)
)
SELECT * FROM SPECIAL
If we turn on NPS compatibility, you see a couple of special characters change behavior. Specifically the ^ operator becomes a "power" operator, and the # becomes an XOR operator.
In [ ]:
%%sql
SET SQL_COMPAT = 'NPS';
WITH SPECIAL(OP, DESCRIPTION, EXAMPLE, RESULT) AS
(
VALUES
(' | ','OR ', '2 | 3 ', 2 | 3),
(' & ','AND ', '2 & 3 ', 2 & 3),
(' ^ ','POWER ', '2 ^ 3 ', 2 ^ 3),
(' ~ ','COMPLIMENT', '~2 ', ~2),
(' # ','XOR ', '2 # 3 ', 2 # 3)
)
SELECT * FROM SPECIAL;
In [ ]:
%%sql
SET SQL_COMPAT='DB2';
SELECT WORKDEPT,INT(AVG(SALARY))
FROM EMPLOYEE
GROUP BY WORKDEPT;
If you try using the ordinal location (similar to an ORDER BY clause), you will get an error message.
In [ ]:
%%sql
SELECT WORKDEPT, INT(AVG(SALARY))
FROM EMPLOYEE
GROUP BY 1;
If NPS compatibility is turned on then then you use the GROUP BY clause with an ordinal location.
In [ ]:
%%sql
SET SQL_COMPAT='NPS';
SELECT WORKDEPT, INT(AVG(SALARY))
FROM EMPLOYEE
GROUP BY 1;
The translate function syntax in Db2 is:
TRANSLATE(expression, to_string, from_string, padding)The TRANSLATE function returns a value in which one or more characters in a string expression might have been converted to other characters. The function converts all the characters in char-string-exp in from-string-exp to the corresponding characters in to-string-exp or, if no corresponding characters exist, to the pad character specified by padding.
If no parameters are given to the function, the original string is converted to uppercase.
In NPS mode, the translate syntax is:
TRANSLATE(expression, from_string, to_string)If a character is found in the from string, and there is no corresponding character in the to string, it is removed. If it was using Db2 syntax, the padding character would be used instead.
Note: If ORACLE compatibility is ON then the behavior of TRANSLATE is identical to NPS mode.
This first example will uppercase the string.
In [ ]:
%%sql
SET SQL_COMPAT = 'NPS';
VALUES TRANSLATE('Hello');
In this example, the letter 'o' will be replaced with an '1'.
In [ ]:
%sql VALUES TRANSLATE('Hello','o','1')
Note that you could replace more than one character by expanding both the "to" and "from" strings. This example will replace the letter "e" with an "2" as well as "o" with "1".
In [ ]:
%sql VALUES TRANSLATE('Hello','oe','12')
Translate will also remove a character if it is not in the "to" list.
In [ ]:
%sql VALUES TRANSLATE('Hello','oel','12')
Reset the behavior back to Db2 mode.
In [ ]:
%sql SET SQL_COMPAT='DB2'
Db2 has the ability to limit the amount of data retrieved on a SELECT statement through the use of the FETCH FIRST n ROWS ONLY clause. In Db2 11, the ability to offset the rows before fetching was added to the FETCH FIRST clause.
The FETCH first clause can be used in a variety of locations in a SELECT clause. This first example fetches only 10 rows from the EMPLOYEE table.
In [ ]:
%%sql
SELECT LASTNAME FROM EMPLOYEE
FETCH FIRST 5 ROWS ONLY
You can also add ORDER BY and GROUP BY clauses in the SELECT statement. Note that Db2 still needs to process all of the records and do the ORDER/GROUP BY work before limiting the answer set. So you are not getting the first 5 rows "sorted". You are actually getting the entire answer set sorted before retrieving just 5 rows.
In [ ]:
%%sql
SELECT LASTNAME FROM EMPLOYEE
ORDER BY LASTNAME
FETCH FIRST 5 ROWS ONLY
Here is an example with the GROUP BY statement. This first SQL statement gives us the total answer set - the count of employees by WORKDEPT.
In [ ]:
%%sql
SELECT WORKDEPT, COUNT(*) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY WORKDEPT
Adding the FETCH FIRST clause only reduces the rows returned, not the rows that are used to compute the GROUPing result.
In [ ]:
%%sql
SELECT WORKDEPT, COUNT(*) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY WORKDEPT
FETCH FIRST 5 ROWS ONLY
The FETCH FIRST n ROWS ONLY clause can also include an OFFSET keyword. The OFFSET keyword allows you to retrieve the answer set after skipping "n" number of rows. The syntax of the OFFSET keyword is:
OFFSET n ROWS FETCH FIRST x ROWS ONLY
The OFFSET n ROWS must precede the FETCH FIRST x ROWS ONLY clause. The OFFSET clause can be used to scroll down an answer set without having to hold a cursor. For instance, you could have the first SELECT call request 10 rows by just using the FETCH FIRST clause. After that you could request the first 10 rows be skipped before retrieving the next 10 rows.
The one thing you must be aware of is that that answer set could change between calls if you use this technique of a "moving" window. If rows are updated or added after your initial query you may get different results. This is due to the way that Db2 adds rows to a table. If there is a DELETE and then an INSERT, the INSERTed row may end up in the empty slot. There is no guarantee of the order of retrieval. For this reason you are better off using an ORDER by to force the ordering although this too won't always prevent rows changing positions.
Here are the first 10 rows of the employee table (not ordered).
In [ ]:
%%sql
SELECT LASTNAME FROM EMPLOYEE
FETCH FIRST 10 ROWS ONLY
You can specify a zero offset to begin from the beginning.
In [ ]:
%%sql
SELECT LASTNAME FROM EMPLOYEE
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY
Now we can move the answer set ahead by 5 rows and get the remaining 5 rows in the answer set.
In [ ]:
%%sql
SELECT LASTNAME FROM EMPLOYEE
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY
The FETCH FIRST/OFFSET clause is not limited to regular SELECT statements. You can also limit the number of rows that are used in a subselect. In this case you are limiting the amount of data that Db2 will scan when determining the answer set.
For instance, say you wanted to find the names of the employees who make more than the average salary of the 3rd highest paid department. (By the way, there are multiple ways to do this, but this is one approach).
The first step is to determine what the average salary is of all departments.
In [ ]:
%%sql
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC;
We only want one record from this list (the third one), so we can use the FETCH FIRST clause with an OFFSET to get the value we want (Note: we need to skip 2 rows to get to the 3rd one).
In [ ]:
%%sql
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY
And here is the list of employees that make more than the average salary of the 3rd highest department in the company.
In [ ]:
%%sql
SELECT LASTNAME, SALARY FROM EMPLOYEE
WHERE
SALARY > (
SELECT AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
OFFSET 2 ROWS FETCH FIRST 1 ROW ONLY
)
ORDER BY SALARY
The FETCH FIRST n ROWS ONLY and OFFSET clause can also be specified using a simpler LIMIT/OFFSET syntax.
The LIMIT clause and the equivalent FETCH FIRST syntax are shown below.
| Syntax | Equivalent |
|---|---|
| LIMIT x | FETCH FIRST x ROWS ONLY |
| LIMIT x OFFSET y | OFFSET y ROWS FETCH FIRST x ROWS ONLY |
| LIMIT y,x | OFFSET y ROWS FETCH FIRST x ROWS ONLY |
The previous examples are rewritten using the LIMIT clause.
We can use the LIMIT clause with an OFFSET to get the value we want from the table.
In [ ]:
%%sql
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
LIMIT 1 OFFSET 2
Here is the list of employees that make more than the average salary of the 3rd highest department in the company. Note that the LIMIT clause specifies only the offset (LIMIT x) or the offset and limit (LIMIT y,x) when you do not use the LIMIT keyword. One would think that LIMIT x OFFSET y would translate into LIMIT x,y but that is not the case. Don't try to figure out the SQL standards reasoning behind the syntax!
In [ ]:
%%sql
SELECT LASTNAME, SALARY FROM EMPLOYEE
WHERE
SALARY > (
SELECT AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
LIMIT 2,1
)
ORDER BY SALARY
A number of new HEX manipulation functions have been added to Db2 11. There are a class of functions that manipulate different size integers (SMALL, INTEGER, BIGINT) using NOT, OR, AND, and XOR. In addition to these functions, there are a number of functions that display and convert values into hexadecimal values.
The INTN functions are bitwise functions that operate on the "two's complement" representation of the integer value of the input arguments and return the result as a corresponding base 10 integer value.
The function names all include the size of the integers that are being manipulated:
There are four functions:
Six variables will be created to use in the examples. The X/Y values will be set to X=1 (01) and Y=3 (11) and different sizes to show how the functions work.
In [ ]:
%%sql -q
DROP VARIABLE XINT2;
DROP VARIABLE YINT2;
DROP VARIABLE XINT4;
DROP VARIABLE YINT4;
DROP VARIABLE XINT8;
DROP VARIABLE YINT8;
CREATE VARIABLE XINT2 INT2 DEFAULT(1);
CREATE VARIABLE YINT2 INT2 DEFAULT(3);
CREATE VARIABLE XINT4 INT4 DEFAULT(1);
CREATE VARIABLE YINT4 INT4 DEFAULT(3);
CREATE VARIABLE XINT8 INT8 DEFAULT(1);
CREATE VARIABLE YINT8 INT8 DEFAULT(3);
This example will show the four functions used against SMALLINT (INT2) data types.
In [ ]:
%%sql
WITH LOGIC(EXAMPLE, X, Y, RESULT) AS
(
VALUES
('INT2AND(X,Y)',XINT2,YINT2,INT2AND(XINT2,YINT2)),
('INT2OR(X,Y) ',XINT2,YINT2,INT2OR(XINT2,YINT2)),
('INT2XOR(X,Y)',XINT2,YINT2,INT2XOR(XINT2,YINT2)),
('INT2NOT(X) ',XINT2,YINT2,INT2NOT(XINT2))
)
SELECT * FROM LOGIC
This example will use the 4 byte (INT4) data type.
In [ ]:
%%sql
WITH LOGIC(EXAMPLE, X, Y, RESULT) AS
(
VALUES
('INT4AND(X,Y)',XINT4,YINT4,INT4AND(XINT4,YINT4)),
('INT4OR(X,Y) ',XINT4,YINT4,INT4OR(XINT4,YINT4)),
('INT4XOR(X,Y)',XINT4,YINT4,INT4XOR(XINT4,YINT4)),
('INT4NOT(X) ',XINT4,YINT4,INT4NOT(XINT4))
)
SELECT * FROM LOGIC
Finally, the INT8 data type is used in the SQL. Note that you can mix and match the INT2, INT4, and INT8 values in these functions but you may get truncation if the value is too big.
In [ ]:
%%sql
WITH LOGIC(EXAMPLE, X, Y, RESULT) AS
(
VALUES
('INT8AND(X,Y)',XINT8,YINT8,INT8AND(XINT8,YINT8)),
('INT8OR(X,Y) ',XINT8,YINT8,INT8OR(XINT8,YINT8)),
('INT8XOR(X,Y)',XINT8,YINT8,INT8XOR(XINT8,YINT8)),
('INT8NOT(X) ',XINT8,YINT8,INT8NOT(XINT8))
)
SELECT * FROM LOGIC
In [ ]:
%sql VALUES TO_HEX(255)
In [ ]:
%sql VALUES RAWTOHEX('Hello')
The string "00" converts to a hex representation of x'3030' which is 12336 in Decimal. So the TO_HEX function would convert this back to the HEX representation.
In [ ]:
%sql VALUES TO_HEX(12336)
The string that is returned by the RAWTOHEX function should be the same.
In [ ]:
%sql VALUES RAWTOHEX('00');
The CREATE TABLE statement can now use a SELECT clause to generate the definition and LOAD the data at the same time.
The syntax of the CREATE table statement has been extended with the AS (SELECT ...) WITH DATA clause:
CREATE TABLEAS (SELECT ...) [ WITH DATA | DEFINITION ONLY ]
The table definition will be generated based on the SQL statement that you specify. The column names are derived from the columns that are in the SELECT list and can only be changed by specifying the columns names as part of the table name: EMP(X,Y,Z,...) AS (...).
For example, the following SQL will fail because a column list was not provided:
In [ ]:
%sql -q DROP TABLE AS_EMP
%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS FROM EMPLOYEE) DEFINITION ONLY;
You can name a column in the SELECT list or place it in the table definition.
In [ ]:
%sql -q DROP TABLE AS_EMP
%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS AS PAY FROM EMPLOYEE) DEFINITION ONLY;
You can check the SYSTEM catalog to see the table definition.
In [ ]:
%%sql
SELECT DISTINCT(NAME), COLTYPE, LENGTH FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME='AS_EMP' AND TBCREATOR=CURRENT USER
The DEFINITION ONLY clause will create the table but not load any data into it. Adding the WITH DATA clause will do an INSERT of rows into the newly created table. If you have a large amount of data to load into the table you may be better off creating the table with DEFINITION ONLY and then using LOAD or other methods to load the data into the table.
In [ ]:
%sql -q DROP TABLE AS_EMP
%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS AS PAY FROM EMPLOYEE) WITH DATA;
The SELECT statement can be very sophisticated. It can do any type of calculation or limit the data to a subset of information.
In [ ]:
%%sql -q
DROP TABLE AS_EMP;
CREATE TABLE AS_EMP(LAST,PAY) AS
(
SELECT LASTNAME, SALARY FROM EMPLOYEE
WHERE WORKDEPT='D11'
FETCH FIRST 3 ROWS ONLY
) WITH DATA;
You can also use the OFFSET clause as part of the FETCH FIRST ONLY to get chunks of data from the original table.
In [ ]:
%%sql -q
DROP TABLE AS_EMP;
CREATE TABLE AS_EMP(DEPARTMENT, LASTNAME) AS
(SELECT WORKDEPT, LASTNAME FROM EMPLOYEE
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY
) WITH DATA;
SELECT * FROM AS_EMP;